"""
考勤管理路由
"""
import io
import os
from datetime import datetime
from flask import Blueprint, render_template, request, redirect, url_for, flash, send_file, current_app
from flask_login import login_required
from sqlalchemy import func, distinct
from werkzeug.utils import secure_filename
import xlsxwriter
import openpyxl

from app.extensions import db
from app.models.staff import Staff
from app.models.attendance import Attendance

attendance_bp = Blueprint('attendance', __name__, url_prefix='/attendance')

# 配置上传文件夹
ALLOWED_EXTENSIONS = {'xlsx', 'xls', 'csv'}


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@attendance_bp.route('/')
@attendance_bp.route('/management')
@login_required
def attendance_management():
    """考勤管理"""
    page = request.args.get('page', 1, type=int)
    search_name = request.args.get('search_name', '')
    search_date = request.args.get('search_date', '')
    
    query = Attendance.query
    
    if search_name:
        query = query.filter(Attendance.staff_name.contains(search_name))
    if search_date:
        search_date_obj = datetime.strptime(search_date, '%Y-%m-%d').date()
        query = query.filter(func.date(Attendance.event_time) == search_date_obj)
    
    attendances = query.order_by(Attendance.event_time.desc()).paginate(page=page, per_page=20, error_out=False)
    
    # 获取员工名单
    staff_names = db.session.query(Staff.name).all()
    
    return render_template('attendance/attendance_management.html',
                         attendances=attendances,
                         staff_names=[s[0] for s in staff_names],
                         search_name=search_name,
                         search_date=search_date)


@attendance_bp.route('/add', methods=['GET', 'POST'])
@login_required
def add_attendance():
    """添加考勤记录"""
    if request.method == 'POST':
        try:
            # 解析时间
            event_time = datetime.strptime(request.form.get('event_time'), '%Y-%m-%dT%H:%M')
            
            attendance = Attendance(
                staff_name=request.form.get('staff_name'),
                project_name=request.form.get('project_name'),
                event_type=request.form.get('event_type'),
                event_time=event_time,
                is_resident=request.form.get('is_resident') == 'on'
            )
            
            db.session.add(attendance)
            db.session.commit()
            flash('考勤记录添加成功')
            return redirect(url_for('attendance.attendance_management'))
        except Exception as e:
            db.session.rollback()
            flash(f'添加失败: {str(e)}')
    
    # GET请求，显示添加表单
    staff_list = Staff.query.all()
    return render_template('attendance/add_attendance.html', staff_list=staff_list)


@attendance_bp.route('/download_template')
@login_required
def download_attendance_template():
    """下载考勤数据上传模板"""
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('考勤数据模板')
    
    # 表头
    headers = ['时间', '人员名称', '所属项目', '事件类型']
    
    # 示例数据
    sample_data = [
        ['2025-8-24 23:01:30', 'XXX', 'XXX项目', '进门'],
        ['2025-8-25 08:30:00', 'XXX', 'XXX项目', '出门']
    ]
    
    # 设置表头格式
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    # 写入表头
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入示例数据
    for row, data in enumerate(sample_data, 1):
        for col, value in enumerate(data):
            worksheet.write(row, col, value)
    
    # 调整列宽
    worksheet.set_column('A:D', 20)
    
    # 添加说明
    worksheet.write(4, 0, '说明：')
    worksheet.write(5, 0, '1. 时间格式：YYYY-M-D H:M:S 或 YYYY-MM-DD HH:MM:SS')
    worksheet.write(6, 0, '2. 事件类型只能填：进门 或 出门')
    worksheet.write(7, 0, '3. 人员名称必须与系统中的员工姓名一致')
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'考勤数据上传模板_{datetime.now().strftime("%Y%m%d")}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@attendance_bp.route('/upload', methods=['POST'])
@login_required
def upload_attendance():
    """批量上传考勤数据"""
    if 'upload_file' not in request.files:
        flash('未选择文件')
        return redirect(url_for('attendance.attendance_management'))
    
    file = request.files['upload_file']
    if file.filename == '':
        flash('未选择文件')
        return redirect(url_for('attendance.attendance_management'))
    
    try:
        # 保存上传的文件
        filename = secure_filename(file.filename)
        upload_folder = current_app.config.get('UPLOAD_FOLDER', 'uploads')
        if not os.path.exists(upload_folder):
            os.makedirs(upload_folder)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        # 解析Excel文件
        workbook = openpyxl.load_workbook(filepath)
        worksheet = workbook.active
        
        success_count = 0
        error_count = 0
        
        # 从第2行开始读取数据
        for row_num, row in enumerate(worksheet.iter_rows(min_row=2, values_only=True), 2):
            if not any(row):
                continue
                
            try:
                event_time_str = str(row[0]) if row[0] else ''
                staff_name = str(row[1]) if row[1] else ''
                project_name = str(row[2]) if row[2] else ''
                event_type = str(row[3]) if row[3] else ''
                
                # 验证必填字段
                if not all([event_time_str, staff_name, event_type]):
                    error_count += 1
                    continue
                
                # 验证事件类型
                if event_type not in ['进门', '出门']:
                    error_count += 1
                    continue
                
                # 解析时间 - 支持多种格式
                try:
                    if len(event_time_str.split(' ')[1].split(':')) == 2:
                        event_time = datetime.strptime(event_time_str.strip(), '%Y-%m-%d %H:%M')
                    else:
                        event_time = datetime.strptime(event_time_str.strip(), '%Y-%m-%d %H:%M:%S')
                except ValueError:
                    try:
                        event_time = datetime.strptime(event_time_str.strip(), '%Y-%m-%d %H:%M:%S')
                    except ValueError:
                        error_count += 1
                        continue
                
                # 查找员工信息
                staff = Staff.query.filter_by(name=staff_name).first()
                if staff:
                    project_name = staff.project_name
                
                # 创建考勤记录
                attendance = Attendance(
                    staff_name=staff_name,
                    project_name=project_name,
                    event_type=event_type,
                    event_time=event_time
                )
                
                db.session.add(attendance)
                success_count += 1
                
            except Exception:
                error_count += 1
        
        # 计算是否驻公司
        calculate_residence_status()
        
        db.session.commit()
        os.remove(filepath)
        
        flash(f'考勤数据上传完成！成功：{success_count}条，失败：{error_count}条')
        
    except Exception as e:
        db.session.rollback()
        flash(f'上传失败：{str(e)}')
    
    return redirect(url_for('attendance.attendance_management'))


@attendance_bp.route('/export')
@login_required
def export_attendance():
    """导出考勤数据"""
    # 获取筛选参数
    search_name = request.args.get('search_name', '')
    search_date = request.args.get('search_date', '')
    
    try:
        # 创建Excel工作簿
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('考勤数据')
        
        # 定义格式
        header_format = workbook.add_format({
            'bold': True,
            'font_size': 12,
            'bg_color': '#4472C4',
            'font_color': 'white',
            'align': 'center',
            'valign': 'vcenter',
            'border': 1
        })
        
        data_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'border': 1
        })
        
        # 写入标题
        worksheet.merge_range('A1:H1', '考勤数据导出表', header_format)
        worksheet.write('A2', f'导出时间: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}')
        if search_name:
            worksheet.write('A3', f'筛选条件 - 员工姓名: {search_name}')
        if search_date:
            worksheet.write('B3', f'日期: {search_date}')
        
        # 写入表头
        headers = ['序号', '员工姓名', '所属项目', '职务', '事件类型', '事件时间', '日期', '是否驻公司']
        for col, header in enumerate(headers):
            worksheet.write(4, col, header, header_format)
        
        # 构建查询
        query = Attendance.query
        
        if search_name:
            query = query.filter(Attendance.staff_name.contains(search_name))
        
        if search_date:
            search_datetime = datetime.strptime(search_date, '%Y-%m-%d')
            query = query.filter(func.date(Attendance.event_time) == search_datetime.date())
        
        # 获取考勤数据
        attendances = query.order_by(Attendance.event_time.desc()).all()
        
        # 写入数据
        row = 5
        for idx, attendance in enumerate(attendances, 1):
            # 获取员工职务信息
            staff = Staff.query.filter_by(name=attendance.staff_name).first()
            project_name = staff.project_name if staff else ''
            position = staff.position if staff else ''
            
            worksheet.write(row, 0, idx, data_format)
            worksheet.write(row, 1, attendance.staff_name, data_format)
            worksheet.write(row, 2, project_name, data_format)
            worksheet.write(row, 3, position, data_format)
            worksheet.write(row, 4, attendance.event_type, data_format)
            worksheet.write(row, 5, attendance.event_time.strftime('%Y-%m-%d %H:%M:%S'), data_format)
            worksheet.write(row, 6, attendance.event_time.strftime('%Y-%m-%d'), data_format)
            
            # 驻公司状态
            if attendance.is_resident is True:
                resident_status = '是'
            elif attendance.is_resident is False:
                resident_status = '否'
            else:
                resident_status = '未知'
            worksheet.write(row, 7, resident_status, data_format)
            row += 1
        
        # 调整列宽
        worksheet.set_column('A:A', 8)   # 序号
        worksheet.set_column('B:B', 15)  # 员工姓名
        worksheet.set_column('C:C', 15)  # 所属项目
        worksheet.set_column('D:D', 12)  # 职务
        worksheet.set_column('E:E', 12)  # 事件类型
        worksheet.set_column('F:F', 20)  # 事件时间
        worksheet.set_column('G:G', 12)  # 日期
        worksheet.set_column('H:H', 12)  # 是否驻公司
        
        # 添加汇总信息
        if attendances:
            summary_row = row + 2
            worksheet.write(summary_row, 0, '汇总统计:', header_format)
            worksheet.write(summary_row + 1, 0, f'总记录数: {len(attendances)}')
            
            # 统计驻公司情况
            resident_count = len([a for a in attendances if a.is_resident is True])
            non_resident_count = len([a for a in attendances if a.is_resident is False])
            worksheet.write(summary_row + 2, 0, f'驻公司记录: {resident_count}')
            worksheet.write(summary_row + 3, 0, f'非驻公司记录: {non_resident_count}')
            
            # 统计进出门次数
            entry_count = len([a for a in attendances if a.event_type == '进门'])
            exit_count = len([a for a in attendances if a.event_type == '出门'])
            worksheet.write(summary_row + 4, 0, f'进门次数: {entry_count}')
            worksheet.write(summary_row + 5, 0, f'出门次数: {exit_count}')
        
        workbook.close()
        output.seek(0)
        
        # 生成文件名
        filename_parts = ['考勤数据']
        if search_name:
            filename_parts.append(f'_{search_name}')
        if search_date:
            filename_parts.append(f'_{search_date}')
        filename_parts.append(f'_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx')
        filename = ''.join(filename_parts)
        
        return send_file(
            output,
            as_attachment=True,
            download_name=filename,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        
    except Exception as e:
        flash(f'导出失败: {str(e)}')
        return redirect(url_for('attendance.attendance_management'))


def calculate_residence_status():
    """计算员工是否驻公司"""
    # 获取所有考勤记录按日期分组
    dates = db.session.query(distinct(func.date(Attendance.event_time))).all()
    
    for date_tuple in dates:
        date = date_tuple[0]
        staff_names = db.session.query(distinct(Attendance.staff_name)).filter(
            func.date(Attendance.event_time) == date
        ).all()
        
        for staff_name_tuple in staff_names:
            staff_name = staff_name_tuple[0]
            
            # 获取该员工当天的所有记录
            daily_records = Attendance.query.filter(
                func.date(Attendance.event_time) == date,
                Attendance.staff_name == staff_name
            ).order_by(Attendance.event_time).all()
            
            # 判断是否驻公司（当晚24点前最后一次是否为出门）
            is_resident = True
            if daily_records:
                last_record = daily_records[-1]
                if last_record.event_type == '出门':
                    is_resident = False
            
            # 更新驻公司状态
            for record in daily_records:
                record.is_resident = is_resident
